﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace WebApplication10.App_Code
{
    public class CoverLetterDetails
    {
        public int CoverLetterID { get; set; }
        public string BodyText { get; set; }

        public static CoverLetterDetails GetCoverLetterDetailsByID(string CoverLetterID)
        {
            SqlConnection conn = new SqlConnection();
            CoverLetterDetails cld = null;
            try
            {
                conn = new SqlConnection();
                string connstr = ConfigurationManager.ConnectionStrings["JobDbString"].ToString();
                conn.ConnectionString = connstr;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from CoverLetterDetails where CoverLetterID=@CoverLetterID";
                comm.Parameters.AddWithValue("@CoverLetterID", CoverLetterID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    cld = new CoverLetterDetails();
                    cld.CoverLetterID = (int)dr["CoverLetterID"];
                    cld.BodyText = (string)dr["BodyText"];
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return cld;
        }

        public static List<CoverLetterDetails> GetCoverLetterDetails() // Select.
        {
            List<CoverLetterDetails> results = new List<CoverLetterDetails>();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["JobDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select * from CoverLetterDetails";
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    CoverLetterDetails cld = new CoverLetterDetails();
                    cld.CoverLetterID = (int)dr["CoverLetterID"];
                    cld.BodyText = (string)dr["DateOfCreation"];
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return results;
        }

        public static void UpdateCoverLetterDetails(CoverLetterDetails cld) // Update.
        {
            SqlConnection conn = new SqlConnection();

            try
            {
                conn = new SqlConnection();
                string connstr = ConfigurationManager.ConnectionStrings["JobDBString"].ToString();
                conn.ConnectionString = connstr;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText =
                    "Update CoverLetter SET BodyText=@BodyText WHERE CoverLetterID=@CoverLetterID";
                comm.Parameters.AddWithValue("@BodyText", cld.BodyText);
                comm.Parameters.AddWithValue("@CoverLetterID", cld.CoverLetterID);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public static void DeleteCoverLetterDetails(CoverLetterDetails cld) // Delete.
        {
            SqlConnection conn = new SqlConnection();

            try
            {
                conn = new SqlConnection();
                string connstr = ConfigurationManager.ConnectionStrings["JobDBString"].ToString();
                conn.ConnectionString = connstr;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText =
                    "Delete from CoverLetter WHERE CoverLetterID=@CoverLetterID";
                comm.Parameters.AddWithValue("@CoverLetterID", cld.CoverLetterID);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public static int InsertCoverLetterDetails(CoverLetterDetails cld) // Insert.
        {
            SqlConnection conn = null;
            int newCoverLetterID = 0;

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["JobDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Insert into CoverLetterDetails (BodyText) VALUES (@BodyText)";
                comm.Parameters.AddWithValue("@BodyText", cld.BodyText);
                int rowsAffected = comm.ExecuteNonQuery();
                comm.CommandText = "select @@IDENTITY as newCoverLetterID";
                SqlDataReader dr = comm.ExecuteReader();
                dr.Read();
                newCoverLetterID = int.Parse(dr["NewCoverLetterID"].ToString());
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return newCoverLetterID;
        }
    }
}